﻿using System;
using System.Collections.Generic;
using System.Data;
using personalplaner.business.common;
using personalplaner.business.stamm;
using personalplaner.core.sql;
using personalplaner.dataprovider.services;
using System.Linq;

namespace personalplaner.dataprovider.dienstplan
{
	public partial class SchemaDataService
	{
		#region Get Methods

		/// <summary>
		/// Creates a list of Schemas. [schemaid,name,status]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="mandid">mandid</param>
		/// <param name="showInactive">get only active?</param>
		/// <returns>[schemaid,name,status]</returns>
		public IEnumerable<PlanSchema> GetSchemas(DatabaseSettings settings, string mandid, bool showInactive)
		{
			int status = 1;
			if (showInactive)
				status = 0;

			var query = new Querry(settings, "get_shemas");
			query.AddParameter("@mandid", mandid);
			query.AddParameter("@status", status);
			var table = query.ExecuteForDataTable();

			try
			{
                return m_planSchemaData(table).OrderByDescending(a => a.SchemaID);                  
			}
			catch (Exception e)
			{
				throw new Exception("GetSchemas caused an error", e);
			}
		}

        /// <summary>
        /// gets a certain schema
        /// </summary>
        /// <param name="settings"></param>
        /// <param name="schemaid"></param>
        /// <returns></returns>
        public PlanSchema GetSchema(DatabaseSettings settings, int schemaid)
        {
            var str = string.Format("select schemaid = SchemaID, name = Name, status = Status from Schemas where SchemaID = {0}", schemaid);
            var table = SimpleQuerry.ExecuteForDataTable(settings, str);

            if (table.Rows.Count <= 0)
                return null;

            try
            {
                return m_planSchemaData(table).FirstOrDefault();
            }
            catch (Exception e)
            {
                throw new Exception("GetSchemas caused an error", e);
            }
        }

		/// <summary>
		/// Creates a list with Schemadata. [schemaname,schemaid,prsschemaid,prsid,abteilungid,rgb,von,bis]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="schemaid">planid</param>
		/// <returns>[schemaname,schemaid,prsschemaid,prsid,abteilungid,abteilungname,rgb,von,bis]</returns>
		public IEnumerable<PlanSchemaItem> GetPlanSchema(DatabaseSettings settings, int schemaid)
		{
			var query = new Querry(settings, "get_planshema");
			query.AddParameter("@schemaid", schemaid);
			var table = query.ExecuteForDataTable();

			try
			{
                return m_planSchemaItemsData(settings, table);
			}
			catch (Exception e)
			{
				throw new Exception("GetSchemas caused an error", e);
			}
		}

		/// <summary>
		/// Only brings Personen that are in the schema. [prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="schemaid">schemaid</param>
		/// <returns>[prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]</returns>
		public IEnumerable<PlanSchemaItem> GetPersonenInSchema(DatabaseSettings settings, int schemaid)
		{
			var query = new Querry(settings, "get_personeninschema");
			query.AddParameter("@schemaid", schemaid);
			var table = query.ExecuteForDataTable();

			try
			{
                return m_planSchemaItemsData(settings, table);
			}
			catch (Exception e)
			{
				throw new Exception("GetPersonenInSchema caused an error", e);
			}
		}

		/// <summary>
		/// Gets a list of all personal contained in the abteilung.[prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="abteilungid">abteilungid</param>
		/// <returns>[prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]</returns>
		public IEnumerable<PlanSchemaItem> GetPersonenInAbteilung(DatabaseSettings settings, int abteilungid)
		{
			var query = new Querry(settings, "get_personeninabteilung");
			query.AddParameter("@abteilungid", abteilungid);
			var table = query.ExecuteForDataTable();

			try
			{
                return m_planSchemaItemsData(settings, table);
			}
			catch (Exception e)
			{
				throw new Exception("GetPersonenInAbteilung caused an error", e);
			}
		}

        /// <summary>
        /// Gets a list of <see cref="Abteilung"/> that are contained in the schema
        /// </summary>
        /// <param name="settings"></param>
        /// <param name="schemaid">schemaid</param>
        /// <returns>[abteilungid,name]</returns>
        public IEnumerable<Abteilung> GetAbteilungenInSchema(DatabaseSettings settings, int schemaid)
        {
            var query = new Querry(settings, "get_abteilungeninschema");
            query.AddParameter("@schemaid", schemaid);
            var table = query.ExecuteForDataTable();

            try
            {
                return AbteilungDataService.m_abteilungenData(table);
            }
            catch (Exception e)
            {
                throw new Exception("GetSchemas caused an error", e);
            }
        }

		#endregion

		#region Set Methods

		/// <summary>
		/// [schemaid]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="name">name</param>
		/// <param name="mandid">mandid</param>
		/// <returns>[schemaid]</returns>
		public int CreateNewSchema(DatabaseSettings settings, string name, string mandid)
		{
			var query = new Querry(settings,"set_schemas");
			query.AddParameter("@name", name);
			query.AddParameter("@mandid", mandid);
			var table = query.ExecuteForDataTable(true);

			if (table.Rows.Count > 0)
			{
				return table.Columns.Contains("schemaid") ? table.Rows[0].Field<int>("schemaid") : 0;
			}

			return 0;
		}

		/// <summary>
		/// Saves a row in a Planschema
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="schemaid"></param>
		/// <param name="schemaitem"></param>
		public bool SaveSchemaRow(DatabaseSettings settings, int schemaid, PlanSchemaItem schemaitem)
		{
			var query = new Querry(settings, "set_planschema");
			query.AddParameter("@SchemaID", schemaid);
			query.AddParameter("@PrsSchemaID", schemaitem.PersonSchemaID);
			query.AddParameter("@PrsID", schemaitem.PrsID);
			query.AddParameter("@AbteilungID", schemaitem.AbteilungID);
			query.AddParameter("@RGB", schemaitem.RGB);
			query.AddParameter("@ColorName", schemaitem.ColorString);
			query.AddParameter("@Bezeichner", "");

			return query.Execute(false);
		}

		#endregion

		#region Delete Methods

		/// <summary>
		/// Deletes all rows from a schema
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="schemaid">schemaid</param>
		public bool DeleteRowsFromSchema(DatabaseSettings settings, int schemaid)
		{
			return SimpleQuerry.Execute(settings, "delete from PlanSchema where SchemaID = " + schemaid);
		}

        /// <summary>
        /// deletes the schema/layout
        /// </summary>
        /// <param name="settings"></param>
        /// <param name="schemaId"></param>
        /// <returns></returns>
        public bool DeleteSchema(DatabaseSettings settings, int schemaId)
        {
            if (!SimpleQuerry.Execute(settings, string.Format("delete from PlanSchema where SchemaID = {0}", schemaId)))
                return false;

            if (!SimpleQuerry.Execute(settings, string.Format("delete from DienstSchema where SchemaID = {0}", schemaId)))
                return false;

            return SimpleQuerry.Execute(settings, string.Format("delete from Schemas where SchemaID = {0}", schemaId));
        }

		#endregion

		#region Update Methods

		/// <summary>
		/// Deactivate = 0; Activate = 1;
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="schemaid"></param>
		/// <param name="status"></param>
		public bool UpdateSchemaStatus(DatabaseSettings settings, int schemaid, bool status)
		{
			return SimpleQuerry.Execute(settings, string.Format("update Schemas set Status = {0} where SchemaID = {1}", status ? 1 : 0, schemaid));
		}

		#endregion
	}
}
